﻿----------------------
-- SPs for Channel --
----------------------

CREATE OR REPLACE PACKAGE CRUD_Channel AS 
    TYPE T_CURSOR IS REF CURSOR;

    FUNCTION GetChannelKey(
        pdevId IN nvarchar2,
        pdevChn IN number,
        pequipId IN nvarchar2,
        pequipPhase IN number)
        RETURN number;
    
    PROCEDURE CreateChannel(
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        pkey OUT number
    );
    
    PROCEDURE ListChannels(
        curChannel OUT T_CURSOR
    );

	PROCEDURE GetLatestChannel(
        curChannel OUT T_CURSOR
    );
END CRUD_Channel;
/

CREATE OR REPLACE PACKAGE BODY CRUD_Channel AS
    FUNCTION GetChannelKey
    (
        pdevId IN nvarchar2,
        pdevChn IN number,
        pequipId IN nvarchar2,
        pequipPhase IN number)
        RETURN number
    AS
        channelKey ChannelTable.Key%type := 0;
    BEGIN
        SELECT
            "KEY" INTO channelKey
        FROM ChannelTable
        WHERE
            DevId = pdevId AND DevChn = pdevChn AND EquipId = pequipId AND EquipPhase = pequipPhase;
        RETURN channelKey;
    EXCEPTION
        WHEN NO_DATA_FOUND
            THEN RETURN 0;
        WHEN OTHERS
            THEN raise_application_error (-20011, 'EXCEPTION IN GetChannelKey');
    END;

    PROCEDURE CreateChannel
    (
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        pkey OUT number
    )
    IS
    BEGIN
        pkey := GetChannelKey(devId, devChn, equipId, equipPhase);
        IF pkey = 0 THEN
            pkey := ChannelSeq.nextval;
            INSERT INTO ChannelTable
            (
                Key, DevId, DevChn, EquipId, EquipPhase
            )
            VALUES
            (
                pkey,
                devId,
                devChn,
                equipId,
                equipPhase
            );
        END IF;
    END;

    PROCEDURE ListChannels
    (
        curChannel OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curChannel FOR
            SELECT Key, DevId, DevChn, EquipId, EquipPhase
            FROM ChannelTable;
    END;

	PROCEDURE GetLatestChannel
	(
        curChannel OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curChannel FOR
            SELECT CT.[Key], CT.DevId, CT.DevChn, CT.EquipId, CT.EquipPhase FROM PDTable
			LEFT JOIN [ChannelTable] CT ON PDTable.ChannelKey = CT.[Key]
			WHERE
				PDTable.[PDTime] = (SELECT MAX(PDTime) From PDTable)
    END;
END CRUD_Channel;
/

----------------------
-- SPs for PDTable --
----------------------

CREATE OR REPLACE PACKAGE CRUD_PD AS
    TYPE T_CURSOR IS REF CURSOR;

    PROCEDURE CreatePD(
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        pdTime IN date,
        pdMean IN real,
        pdMax IN real,
        pdPhase IN real,
        pdRep IN real,
        pdType IN nvarchar2,
        pdTypeProb IN nvarchar2,
        pdLoc IN nvarchar2,
        pdLevel IN nvarchar2,
        pdFile IN nvarchar2,
        prpsGraph IN blob,
        key OUT number
    );

    PROCEDURE ListTimePointsOfChannel (
        pChannelKey IN number,
        curPoints OUT T_CURSOR
    );

	PROCEDURE ListSingalsOfChannel (
        pChannelKey IN number,
        curPoints OUT T_CURSOR
    );

    PROCEDURE GetSignal (
        pKey IN number,
        curSignal OUT T_CURSOR
    );

    PROCEDURE ListSignals (
        curSignals OUT T_CURSOR
    );
	
    PROCEDURE ListSignalsByChannelAndTime (
        pChannelKey IN number,
        startTime IN date,
        endTime IN date,
        curSignals OUT T_CURSOR
    );

END CRUD_PD;
/

CREATE OR REPLACE PACKAGE BODY CRUD_PD AS

    PROCEDURE CreatePD(
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        pdTime IN date,
        pdMean IN real,
        pdMax IN real,
        pdPhase IN real,
        pdRep IN real,
        pdType IN nvarchar2,
        pdTypeProb IN nvarchar2,
        pdLoc IN nvarchar2,
        pdLevel IN nvarchar2,
        pdFile IN nvarchar2,
        prpsGraph IN blob,
        key OUT number
    )
    IS
        channelKey number;
    BEGIN
        CRUD_Channel.CreateChannel (
            devId, 
            devChn, 
            equipId, 
            equipPhase, 
            channelKey);

        key := PDSeq.nextval;
        INSERT INTO PDTable (
            Key,
            ChannelKey,
            PDTime,
            PDMean,
            PDMax,
            PDPhase,
            PDRep,
            PDType,
            PDTypeProb,
            PDLoc,
            PDLevel,
            PDFile,
            PRPSGraph
        )
        VALUES (
            key,
            channelKey,
            pDTime,
            pDMean,
            pDMax,
            pDPhase,
            pDRep,
            pDType,
            pDTypeProb,
            pDLoc,
            pDLevel,
            pDFile,
            pRPSGraph
        );
    END;

    PROCEDURE ListTimePointsOfChannel (
        pChannelKey IN number,
        curPoints OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curPoints FOR
            SELECT Key, PDTime FROM PDTable WHERE
                ChannelKey = pChannelKey;
    END;

	PROCEDURE ListSingalsOfChannel (
        pChannelKey IN number,
        curPoints OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curPoints FOR
            SELECT PDTable.Key
              ,ChannelKey
              ,PDTime
              ,PDMean
              ,PDMax
              ,PDPhase
              ,PDRep
              ,PDType
              ,PDTypeProb
              ,PDLoc
              ,PDLevel
              ,PDFile
              ,CT.DevId
              ,CT.DevChn
              ,CT.EquipId
              ,CT.EquipPhase 
              FROM PDTable
            LEFT JOIN ChannelTable CT ON PDTable.ChannelKey = CT.Key WHERE
                ChannelKey = pChannelKey;
    END;

    PROCEDURE GetSignal (
        pKey IN number,
        curSignal OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curSignal FOR
            SELECT PDTable.*, CT.DevId, CT.DevChn, CT.EquipId, CT.EquipPhase FROM PDTable
            LEFT JOIN ChannelTable CT ON PDTable.ChannelKey = CT.Key
            WHERE
                PDTable.Key = pKey;
    END;

    PROCEDURE ListSignals (
        curSignals OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curSignals FOR
            SELECT PDTable.Key
              ,ChannelKey
              ,PDTime
              ,PDMean
              ,PDMax
              ,PDPhase
              ,PDRep
              ,PDType
              ,PDTypeProb
              ,PDLoc
              ,PDLevel
              ,PDFile
              ,CT.DevId
              ,CT.DevChn
              ,CT.EquipId
              ,CT.EquipPhase 
              FROM PDTable
            LEFT JOIN ChannelTable CT ON PDTable.ChannelKey = CT.Key;
    END;

    PROCEDURE ListSignalsByChannelAndTime (
        pChannelKey IN number,
        startTime IN date,
        endTime IN date,
        curSignals OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curSignals FOR
            SELECT PDTable.Key
              ,PDTime
              ,PDMean
              ,PDMax
              ,PDPhase
              ,PDRep
              ,PDType
              ,PDTypeProb
              ,PDLoc
              ,PDLevel
              ,PDFile
            FROM PDTable
            WHERE 
                ChannelKey = channelKey
                AND PDTime BETWEEN startTime AND endTime;
    END;

END CRUD_PD;
/

-------------------------
-- SPs for AlarmTable --
-------------------------

CREATE OR REPLACE PACKAGE CRUD_Alarm AS
    TYPE T_CURSOR IS REF CURSOR;

    PROCEDURE CreateAlarm (
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        alarmTime IN date,
        alarmType IN nvarchar2,
        alarmLevel IN number,
        alarmValue IN real,
        key OUT number
    );

    PROCEDURE ListAlarmsAfter (
        pKey IN number,
        curAlarms OUT T_CURSOR
    );

    PROCEDURE ListLastAlarmForEachChannel (
        curAlarms OUT T_CURSOR
    );
END CRUD_Alarm;
/

CREATE OR REPLACE PACKAGE BODY CRUD_Alarm AS
    PROCEDURE CreateAlarm (
        devId IN nvarchar2,
        devChn IN number,
        equipId IN nvarchar2,
        equipPhase IN number,
        alarmTime IN date,
        alarmType IN nvarchar2,
        alarmLevel IN number,
        alarmValue IN real,
        key OUT number
    )
    IS
        channelKey number;
    BEGIN
        CRUD_Channel.CreateChannel (
            devId, 
            devChn, 
            equipId, 
            equipPhase, 
            channelKey);

        key := AlarmSeq.nextval;

        INSERT INTO AlarmTable (
            Key,
            ChannelKey,
            AlarmTime,
            AlarmType,
            AlarmLevel,
            AlarmValue
        )
        VALUES (
            key,
            channelKey,
            alarmTime,
            alarmType,
            alarmLevel,
            alarmValue
        );
    END;

    PROCEDURE ListAlarmsAfter (
        pKey IN number,
        curAlarms OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curAlarms FOR
            SELECT AlarmTable.*
              ,CT.DevId
              ,CT.DevChn
              ,CT.EquipId
              ,CT.EquipPhase
            FROM AlarmTable
            LEFT JOIN ChannelTable CT ON AlarmTable.ChannelKey = CT.Key
            WHERE
                AlarmTable.Key > pkey;
    END;

    PROCEDURE ListLastAlarmForEachChannel (
        curAlarms OUT T_CURSOR
    )
    IS
    BEGIN
        OPEN curAlarms FOR
            SELECT AlarmTable.*
              ,CT.DevId
              ,CT.DevChn
              ,CT.EquipId
              ,CT.EquipPhase 
            FROM 
            (
                SELECT MAX(Key) Key FROM AlarmTable
                    GROUP BY ChannelKey
            ) Keys
            INNER JOIN AlarmTable ON AlarmTable.Key = Keys.Key
            LEFT JOIN ChannelTable CT ON AlarmTable.ChannelKey = CT.Key;
    END;

END CRUD_Alarm;
/

